<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.ShoppingCartMapper">
    <resultMap id="cartitem" type="models.entities.ShoppingCartItem">
        <result property="title" column="title"/>
        <result property="quantity" column="quantity"/>
        <result property="productId" column="productId"/>
        <result property="price" column="price"/>
    </resultMap>
    <insert id="addProduct">
        insert into shopping_items(productId, userId, quantity, unitPrice)
        select #{productId}, #{userId}, #{quantity}, (select price from products where productId = #{productId} limit 1)
        from dual
        where not exists
        (select * from shopping_items where productId = #{productId} and userId = #{userId});
        update shopping_items set quantity = quantity + 1 where productId = #{productId} and userId = #{userId};
    </insert>
    <select id="getItems" resultMap="cartitem">
        select sum(shop.quantity) as quantity, p.price, p.productId, p.title from shopping_items shop
        left join products p on p.productId = shop.productId
        where shop.userId = #{userId}
        group by p.productId
    </select>
    <delete id="delete">
       delete from shopping_items where userId = #{userId} and productId = #{productId};
    </delete>

</mapper>